# _*_ coding:utf-8 _*_
# @Time  : 2023.03.07
# @Author: zizlee
import datetime
import pathlib
import shutil
import zipfile
from typing import List
from enum import Enum
from fastapi import APIRouter, Header, Body, Query, Form, UploadFile
from fastapi.encoders import jsonable_encoder
from pydantic import BaseModel, validator, constr, conint
from reportlab.lib.pagesizes import A4
from reportlab.lib.units import cm
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Image, Table, TableStyle
from reportlab.lib.styles import ParagraphStyle

from docx import Document
from docx.shared import RGBColor, Pt, Cm
from docx.oxml.ns import qn
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT

from hutool import security
from global_constant import VARIETY
from db import FAConnection
from v1_all_api import constant
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datetime_utils, file_utils, char_utils
from configs import FILE_STORAGE, STATIC_FILE_HOST

period_report_api = APIRouter()

""" --------------- 一些公用类和函数 ------------------- """


class FileTypeEnum(Enum):
    EARLY = 'Early'
    DAILY = 'Daily'
    WEEKLY = 'Weekly'
    MONTHLY = 'Monthly'
    ANNUAL = 'Annual'
    SEASON = 'Season'


class FileQueryArea(Enum):
    Own = '0'
    Group = '1'
    Dept = '2'


def get_user_obj_info(cursor, user_id):
    # 查询用户，组长，组员，管理员
    user_sql = """
        SELECT ru.id,ru.admin_name,ru.role_code,
               wu.group_en,wu.is_leader
        FROM ruizy_user As ru
        LEFT JOIN work_user_extend As wu ON ru.id=wu.user_id AND wu.end_date>=%s
        WHERE ru.id=%s;
    """
    cursor.execute(user_sql, [datetime_utils.today(), user_id])
    u_obj = cursor.fetchone()
    return u_obj


""" ---------------- 一些验证类 -------------------- """


class FileMergeParam(BaseModel):
    file_date: str
    file_vcode: str
    file_name: str
    file_type: FileTypeEnum


class ExamineType(Enum):
    Dept = 'Dept'
    Group = 'Group'


class ExamineReportItem(BaseModel):
    id: int
    examine_state: conint(ge=-1, le=1)
    examine_note: str
    ex_type: ExamineType


class MarkReportItem(BaseModel):
    rid: int
    mark_text: str
    mark_state: int = 1


""" ---------------- APIS -------------------- """


@period_report_api.post('/sliceUpload/')  # 报告分片上传
async def slice_upload_monthly(
        ruizy_token: str = Header(...),
        chunk_index: int = Form(...),
        chunk_count: int = Form(...),
        chunk_data: UploadFile = Form(...),
        file_date: str = Form(...),
        file_vcode: str = Form(...),
        file_name: str = Form(...),
        file_type: FileTypeEnum = Form(...)
):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 使用file_name,user_id,vcode,file_date,file_type哈希出缓存文件夹
    cache_folder = char_utils.hash_string(src_string=file_name,
                                          salt={'uid': str(person['uid']), 'vcode': file_vcode, 'date': file_date, 'file_type': file_type.value})
    abs_cache_folder = pathlib.Path(f'{FILE_STORAGE}').joinpath(f'Temp/{file_type.value}').joinpath(cache_folder)
    if not abs_cache_folder.exists():
        abs_cache_folder.mkdir(parents=True)

    cache_file = f'{chunk_index}_{chunk_count}_{file_date.replace("-", "")}_{file_vcode}_{file_type.value}.tmp'
    with open(abs_cache_folder.joinpath(cache_file), 'wb') as f:
        f.write(await chunk_data.read())

    return AllResponse.operate_successfully()


@period_report_api.post('/mergeUpload/')  # 请求文件合并写入数据库
def merge_upload_monthly(ruizy_token: str = Header(...), merge: FileMergeParam = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    cache_folder = char_utils.hash_string(src_string=merge.file_name,
                                          salt={'uid': str(person['uid']), 'vcode': merge.file_vcode, 'date': merge.file_date, 'file_type': merge.file_type.value})
    abs_cache_folder = pathlib.Path(f'{FILE_STORAGE}').joinpath(f'Temp/{merge.file_type.value}').joinpath(cache_folder)
    if not abs_cache_folder.exists():
        return AllResponse.no_do_anything(msg='合并失败，文件不存在')

    # 保存文件
    save_path, sql_path = file_utils.get_file_paths(module_name=f'PeriodicReport/{merge.file_type.value}', filename=merge.file_name, hashed=True)
    with open(save_path, 'ab+') as fp:
        for slice_file in sorted(abs_cache_folder.glob('*.tmp')):
            with open(slice_file, 'rb') as fs:
                fp.write(fs.read())

    # 写入SQL
    # 名称转为半角
    title = char_utils.strQ2B(merge.file_name)
    title = file_utils.remove_filename_number_suffix(title, number_max=10)  # 去除数据编号和后缀
    # 添加数据库记录
    insert_sql = """
        INSERT INTO report_periodic_file (file_date,creator_id,variety_en,title,file_type,filepath)
        VALUES (%s,%s,%s,%s,%s,%s);
    """
    insert_prm = [merge.file_date.replace('-', ''), person['uid'], merge.file_vcode, title, merge.file_type.value.upper(), sql_path]
    with FAConnection() as cursor:
        c = cursor.execute(insert_sql, insert_prm)
    msg = '合并文件: {}成功!'.format(merge.file_name)
    res = True
    if c < 1:
        # 移除文件
        tmp_file = pathlib.Path(save_path)
        if tmp_file.exists() and tmp_file.is_file():
            tmp_file.unlink()
        msg = '合并完成，但没有添加文件.'
        res = False
    else:
        # 删除缓存的文件
        shutil.rmtree(abs_cache_folder, ignore_errors=True)
    return AllResponse.operate_successfully(data={'merge': res, 'msg': msg})


@period_report_api.get('/belong/')  # 用户查询上传的报告isd=is delete
def belong_monthly_report(
        ruizy_token: str = Header(...),
        page: int = Query(1), page_size: int = Query(50),
        ft: FileTypeEnum = Query(...),
        isd: int = Query(0, ge=0, le=1)
):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        SELECT id,create_time,file_date,creator_id,variety_en,title,file_type,filepath,examine_time,examine_name,examine_state,examine_note,
            dept_examine_name,dept_examine_state,dept_examine_note,is_delete
        FROM report_periodic_file WHERE creator_id=%s AND file_type=%s AND is_delete=%s ORDER BY create_time DESC,file_date DESC;
    """
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, param=[person['uid'], ft.value.upper(), isd], page=page, page_size=page_size)
    for row in ret['data']:
        row['create_time'] = str(row['create_time'])
        row['file_date'] = datetime_utils.format_integer_date(row['file_date'], rf='%Y-%m-%d', obj=False)
        row['variety_name'] = VARIETY.VARIETY_NAMES.get(row['variety_en'])
        row['examine_text'] = constant.EXAMINE_STATES.get(row['examine_state'])
        row['dept_examine_text'] = constant.EXAMINE_STATES.get(row['dept_examine_state'])
        row['file_url'] = STATIC_FILE_HOST + row['filepath']
    return AllResponse.operate_successfully(data=ret)


@period_report_api.delete('/belong/')  # 用户删除或回收自己的报告
def delete_belong_report(ruizy_token: str = Header(...), rid: int = Query(...), action: int = Query(1, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    with FAConnection() as cursor:
        # 查询报告资料
        cursor.execute("SELECT id,examine_state,dept_examine_state FROM report_periodic_file WHERE id=%s LIMIT 1;", [rid])
        _obj = cursor.fetchone()
        if _obj and (_obj['examine_state'] != 0 or _obj['dept_examine_state'] != 0):
            return AllResponse.validate_error(msg='已审核操作过的报告不再支持此操作了...')
        cursor.execute('UPDATE report_periodic_file SET is_delete=%s WHERE id=%s AND creator_id=%s LIMIT 1;',
                       [action, rid, person['uid']])
    return AllResponse.operate_successfully()


# examine:-1,0,1[拒绝,待审,通过];
# rt:报告类型(FileTypeEnum);
# area:个人0-组1-部门2
@period_report_api.get('/examineList/')  # 获取审核列表
def examine_report_list(ruizy_token: str = Header(...), examine: int = Query(0, ge=-1, le=1), rt: FileTypeEnum = Query(...), area: FileQueryArea = Query(...),
                        mark: int = Query(0),
                        page: int = Query(1), page_size: int = Query(30)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    # 查询报告的sqls
    rpt_dept_sql = """
        SELECT rp.id,rp.file_date,rp.variety_en,rp.title,rp.file_type,rp.filepath,rp.examine_state,rp.examine_note,
               rp.dept_examine_state,rp.dept_examine_note,rp.mark_state,rp.mark_note,
               rp.create_time,
               ru.admin_name
        FROM report_periodic_file As rp
        INNER JOIN ruizy_user As ru ON rp.creator_id=ru.id AND rp.examine_state=1
        WHERE rp.file_type=%s AND rp.dept_examine_state=%s AND IF(%s=0,TRUE,rp.mark_state=%s) AND rp.is_delete=0
        ORDER BY rp.create_time DESC;
    """
    rpt_group_sql = """
        SELECT rp.id,rp.file_date,rp.variety_en,rp.title,rp.file_type,rp.filepath,rp.examine_state,rp.examine_note,
               rp.dept_examine_state,rp.dept_examine_note,rp.mark_state,rp.mark_note,
               rp.create_time,
               ru.admin_name
        FROM report_periodic_file As rp
        INNER JOIN ruizy_user As ru ON rp.creator_id=ru.id
        WHERE rp.file_type=%s AND rp.creator_id IN (SELECT user_id FROM work_user_extend WHERE group_en=%s AND end_date>=%s)
              AND rp.examine_state=%s AND rp.is_delete=0
        ORDER BY rp.create_time DESC;
    """
    rtp_own_sql = """
        SELECT rp.id,rp.file_date,rp.variety_en,rp.title,rp.file_type,rp.filepath,rp.examine_state,rp.examine_note,
               rp.dept_examine_state,rp.dept_examine_note,rp.mark_state,rp.mark_note,
               rp.create_time,
               ru.admin_name
        FROM report_periodic_file As rp
        INNER JOIN ruizy_user As ru ON rp.creator_id=ru.id
        WHERE rp.file_type=%s AND rp.creator_id=%s AND rp.examine_state=%s AND rp.is_delete=0
        ORDER BY rp.create_time DESC;
    """
    with FAConnection() as cursor:
        u_obj = get_user_obj_info(cursor, person['uid'])
        # print('user_obj', u_obj)
        if not u_obj:
            return AllResponse.operate_successfully(data=[])

        if area == FileQueryArea.Dept and u_obj['role_code'] == constant.ADMIN_ROLE_CODE:  # 查询部门的需要是管理员
            if mark == 1:
                examine = 1
            ret = cursor.self_conn.query_paginator(rpt_dept_sql, [rt.value.upper(), examine, mark, mark], page=page, page_size=page_size, keep_conn=True)
            # print('查询部门')
        elif area == FileQueryArea.Group and u_obj['is_leader']:  # 组
            ret = cursor.self_conn.query_paginator(rpt_group_sql, [rt.value.upper(), u_obj['group_en'], datetime_utils.today(), examine],
                                                   page=page, page_size=page_size, keep_conn=True)
            # print('查询组')
        else:

            ret = cursor.self_conn.query_paginator(rtp_own_sql, [rt.value.upper(), u_obj['id'], examine], page=page, page_size=page_size, keep_conn=True)
            # print('查询个人')
    for row in ret['data']:
        row['file_date'] = datetime_utils.format_integer_date(row['file_date'], rf='%Y-%m-%d')
        row['file_url'] = STATIC_FILE_HOST + row['filepath']
        row['create_time'] = str(row['create_time'])
        row['variety_name'] = VARIETY.VARIETY_NAMES.get(row['variety_en'])
    return AllResponse.operate_successfully(data=ret)


@period_report_api.put('/examineList/')  # 审核报告列表
def examine_report_many(ruizy_token: str = Header(...), rows: List[ExamineReportItem] = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if len(rows) < 1:
        return AllResponse.no_do_anything(msg='您没有上传任何数据!')

    dict_rows = jsonable_encoder(rows)

    exec_sql = """
        UPDATE report_periodic_file SET
        examine_time=NOW(),examine_name=%(examine_name)s,
        examine_state=%(examine_state)s,examine_note=%(examine_note)s
        WHERE id=%(id)s;
    """
    update_dept_state = False
    with FAConnection() as cursor:
        u_obj = get_user_obj_info(cursor, person['uid'])
        if not u_obj:
            return AllResponse.forbidden_error(msg='未知身份，无法进行操作!')
        for r in dict_rows:
            r['dept_examine_state'] = r['examine_state']
            r['dept_examine_note'] = r['examine_note']
            r['dept_examine_name'] = u_obj['admin_name']
            r['examine_name'] = u_obj['admin_name']
        if dict_rows[0]['ex_type'] == ExamineType.Dept.value:
            exec_sql = """
               UPDATE report_periodic_file SET
               dept_examine_time=NOW(),dept_examine_name=%(dept_examine_name)s,
               dept_examine_state=%(dept_examine_state)s,dept_examine_note=%(dept_examine_note)s
               WHERE id=%(id)s;
           """
        else:
            update_dept_state = True
        effect_rows = cursor.executemany(exec_sql, dict_rows)
        if update_dept_state:
            cursor.execute("UPDATE report_periodic_file SET dept_examine_state=-2 WHERE examine_state=-1 AND id in %s;", [[r['id'] for r in dict_rows]])
    return AllResponse.operate_successfully(msg=f'操作成功，共{effect_rows}个数据受影响!')


@period_report_api.get('/adminReports/')  # 管理报告列表
def admin_report_list(ruizy_token: str = Header(...), rt: FileTypeEnum = Query(...), uid: int = Query(0),
                      start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sdate = datetime_utils.auth_datetime_string(start, f'%Y%m%d')
    edate = datetime_utils.auth_datetime_string(end, f'%Y%m%d')
    if not sdate or not edate:
        return AllResponse.validate_error('查询日期错误！')
    sql = """
        SELECT rp.id,rp.file_date,rp.variety_en,rp.title,rp.file_type,rp.filepath,
               rp.create_time,rp.mark_state,rp.mark_note,
               rp.creator_id,ru.admin_name
        FROM report_periodic_file As rp
        INNER JOIN ruizy_user As ru ON rp.creator_id=ru.id 
        WHERE rp.file_type=%s AND rp.examine_state=1 AND rp.dept_examine_state=1 AND rp.is_delete=0
              AND rp.file_date>=%s AND rp.file_date<=%s
    """
    prm = [rt.value.upper(), sdate, edate]
    if uid > 0:
        sql += ' AND rp.creator_id=%s '
        prm.append(uid)

    sql += 'ORDER BY rp.create_time DESC;'
    with FAConnection() as cursor:
        cursor.execute(sql, prm)
        report_list = cursor.fetchall()
    for r in report_list:
        r['create_time'] = str(r['create_time'])
        r['file_date'] = datetime_utils.format_integer_date(r['file_date'], rf='%Y-%m-%d')
        r['file_url'] = STATIC_FILE_HOST + r['filepath']
        r['variety_name'] = VARIETY.VARIETY_NAMES.get(r['variety_en'])
    return AllResponse.operate_successfully(data={'rows': report_list})


@period_report_api.get('/adminReportsPackage/')  # 管理打包下载
def package_report_list(ruizy_token: str = Header(...), rt: FileTypeEnum = Query(...), uid: int = Query(0),
                        start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sdate = datetime_utils.auth_datetime_string(start, f'%Y%m%d')
    edate = datetime_utils.auth_datetime_string(end, f'%Y%m%d')
    if not sdate or not edate:
        return AllResponse.validate_error('查询日期错误！')
    sql = """
            SELECT rp.id,rp.file_date,rp.variety_en,rp.title,rp.file_type,rp.filepath,
                   rp.create_time,
                   ru.admin_name
            FROM report_periodic_file As rp
            INNER JOIN ruizy_user As ru ON rp.creator_id=ru.id 
            WHERE rp.file_type=%s AND rp.examine_state=1 AND rp.dept_examine_state=1 AND rp.is_delete=0
                  AND rp.file_date>=%s AND rp.file_date<=%s
        """
    prm = [rt.value.upper(), sdate, edate]
    if uid > 0:
        sql += ' AND rp.creator_id=%s '
        prm.append(uid)

    sql += 'ORDER BY rp.create_time DESC;'
    with FAConnection() as cursor:
        cursor.execute(sql, prm)
        report_list = cursor.fetchall()

    target_folder = pathlib.Path(FILE_STORAGE).joinpath(f'Temp/zip_{datetime_utils.today_timestamp(millisecond=Table)}')
    if not target_folder.exists():
        target_folder.mkdir(parents=True)

    zip_path = pathlib.Path(FILE_STORAGE).joinpath(f'Temp/文件下载_{datetime_utils.today_timestamp(millisecond=Table)}.zip')

    with zipfile.ZipFile(zip_path, 'w', zipfile.ZIP_DEFLATED) as zf:
        folder = zipfile.ZipInfo('Files/')
        folder.external_attr = 0x10
        zf.writestr(folder, '')
        for r in report_list:
            t_file = r['title'] + pathlib.Path(r['filepath']).suffix
            try:
                dest_file = shutil.copy(pathlib.Path(FILE_STORAGE).joinpath(r['filepath']), target_folder.joinpath(t_file))
                zf.write(dest_file, f'Files/{t_file}')
            except Exception:
                continue
    shutil.rmtree(target_folder, ignore_errors=True)
    zip_filepath = str(zip_path).replace('\\', '/').replace(FILE_STORAGE, '')
    return AllResponse.operate_successfully(data={'zip': STATIC_FILE_HOST + zip_filepath}, msg='打包成功！')


@period_report_api.post('/adminMarkReport/')  # 管理标记报告
def admin_mark_report(ruizy_token: str = Header(...), mark: MarkReportItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        UPDATE report_periodic_file SET mark_time=NOW(),mark_name=(SELECT admin_name FROM ruizy_user WHERE id=%s),mark_state=%s,mark_note=%s
        WHERE id=%s LIMIT 1;
    """
    mark_text = mark.mark_text.strip()
    if not mark_text:
        mark.mark_state = 0
    with FAConnection() as cursor:
        count = cursor.execute(sql, [person['uid'], mark.mark_state, mark_text, mark.rid])
    return AllResponse.operate_successfully(msg=f'标记成功，影响行数:{count}')


# 删除报告文件记录
@period_report_api.delete('/')
def delete_period_report(ruizy_token: str = Header(...), rid: int = Query(...), action: int = Query(1, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_super_admin(person):
        return AllResponse.forbidden_error()
    # 删除或restore
    with FAConnection() as cursor:
        cursor.execute('UPDATE report_periodic_file SET is_delete=%s WHERE id=%s LIMIT 1;',
                       [action, rid])
    return AllResponse.operate_successfully()


class PeriodFileType(Enum):
    EARLY = 'EARLY'
    DAILY = 'DAILY'
    WEEKLY = 'WEEKLY'


# 查询已经删除的报告
@period_report_api.get('/restore/')
def restore_period_report(ruizy_token: str = Header(...), t: PeriodFileType = Query(...),
                          page: int = Query(1), page_size: int = Query(30)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        SELECT a.id,a.create_time,a.file_date,a.file_type,a.creator_id,a.variety_en,a.title,a.filepath,
            a.examine_state,a.examine_time,u.admin_name
        FROM report_periodic_file As a INNER JOIN ruizy_user AS u ON a.creator_id=u.id
        WHERE a.file_type=%s AND a.is_delete=1 ORDER BY a.file_date DESC;
    """
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, param=[t.value], page=page, page_size=page_size)
    examines = {0: '待审核', 1: '审核通过', -1: '审核不通过'}
    if t == PeriodFileType.EARLY:
        for row in ret['data']:
            # pdf
            row['pdf_file'] = STATIC_FILE_HOST + row['filepath']
            # 查看是否有word文件
            row['word_file'] = ''
            word_file = pathlib.Path(FILE_STORAGE).joinpath(row['filepath']).with_suffix('.docx')
            if word_file.exists():
                row['word_file'] = STATIC_FILE_HOST + str(word_file).replace('\\', '/').replace(FILE_STORAGE, '')
            del row['filepath']
    elif t == PeriodFileType.DAILY:
        for row in ret['data']:
            row['pdf_file'] = STATIC_FILE_HOST + row['filepath']
            row['variety_name'] = VARIETY.VARIETY_NAMES.get(row['variety_en'], row['variety_en'])
            row['examine_text'] = examines.get(row['examine_state'])
            row['examine_time'] = row['examine_time'].strftime('%Y-%m-%d %H:%M:%S') if row['examine_time'] else None
    return AllResponse.operate_successfully(data=ret)


class ReportAutorType(Enum):
    Dept = 'dept'  # 部门人员（全部人员）
    Group = 'group'  # 小组人员
    Own = 'own'  # 仅自己


# 获取定期报告的文件列表(管理查询，小组查询，个人查询)
@period_report_api.get('/fileList/')
def query_periodic_report_file(ruizy_token: str = Header(...), t: PeriodFileType = Query(...),
                               page: int = Query(...), page_size: int = Query(...),
                               aut: ReportAutorType = Query(ReportAutorType.Dept)):
    # aut返回的报告作者情况
    # 管理员就查询所有数据，非管理员只返回作者是自己的，own不管是什么身份只查询自己的
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        SELECT a.id,a.create_time,a.file_date,a.creator_id,a.variety_en,a.title,a.filepath,
            a.examine_state,a.examine_time,a.examine_name,u.admin_name
        FROM report_periodic_file As a INNER JOIN ruizy_user AS u ON a.creator_id=u.id
        WHERE a.file_type=%s AND a.is_delete=0 
    """
    prm = [t.value]
    if aut == ReportAutorType.Dept:  # 所有人员的报告
        sql += ' ORDER BY a.file_date DESC;'
    elif aut == ReportAutorType.Group:  # 小组人员的报告
        # 查询当前用户的小组成员
        with FAConnection() as cursor:
            cursor.execute("""
                            SELECT id,user_id,group_en,is_leader,admin_name FROM work_user_extend 
                            WHERE group_en=(SELECT group_en FROM work_user_extend WHERE user_id=%s) 
                            AND end_date>=%s;
                        """, [person['uid'], datetime.date.today().strftime('%Y%m%d')])
            group_users = cursor.fetchall()
            is_leader = False
            for user in group_users:
                if user['is_leader'] == 1 and user['user_id'] == person['uid']:
                    is_leader = True
                    break
            users = ([str(person['uid'])] + [str(u['user_id']) for u in group_users]) if is_leader else [str(person['uid'])]
            sql += ' AND a.creator_id IN (%s) ORDER BY a.file_date DESC;' % ','.join(users)
    elif aut == ReportAutorType.Own:
        sql += ' AND a.creator_id=%s ORDER BY a.file_date DESC;'
        prm.append(person['uid'])
    else:
        sql += ' AND a.creator_id=%s ORDER BY a.file_date DESC;'
        prm.append(person['uid'])
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, prm, page=page, page_size=page_size)
    examines = {0: '待审核', 1: '审核通过', -1: '审核不通过'}
    if t == PeriodFileType.EARLY:
        for row in ret['data']:
            # pdf
            row['pdf_file'] = STATIC_FILE_HOST + row['filepath']
            # 查看是否有word文件
            row['word_file'] = ''
            word_file = pathlib.Path(FILE_STORAGE).joinpath(row['filepath']).with_suffix('.docx')
            if word_file.exists():
                row['word_file'] = STATIC_FILE_HOST + str(word_file).replace('\\', '/').replace(FILE_STORAGE, '')
            del row['filepath']
    elif t == PeriodFileType.DAILY:
        for row in ret['data']:
            row['pdf_file'] = STATIC_FILE_HOST + row['filepath']
            row['variety_name'] = VARIETY.VARIETY_NAMES.get(row['variety_en'], row['variety_en'])
            row['examine_text'] = examines.get(row['examine_state'])
            row['examine_time'] = row['examine_time'].strftime('%Y-%m-%d %H:%M:%S') if row['examine_time'] else None
    return AllResponse.operate_successfully(data=ret)


# 按日期获取报告列表(不分类别)
@period_report_api.get('/dateFileList/')
def query_period_report(ruizy_token: str = Header(...), date: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    query_date = datetime_utils.auth_datetime_string(date, f='%Y%m%d')
    if not query_date:
        return AllResponse.validate_error(msg='查询参数错误')
    with FAConnection() as cursor:
        cursor.execute('SELECT * FROM report_periodic_file WHERE file_date=%s AND is_delete=0;', [query_date])
        file_list = list(cursor.fetchall())
    return AllResponse.operate_successfully(data=file_list)


@period_report_api.get('/latestFile/')
def latest_report_file(page: int = Query(...), page_size: int = Query(...)):
    sql = """
        SELECT a.id,a.create_time,a.file_date,a.creator_id,a.variety_en,a.title,a.filepath,
            a.examine_state,a.examine_time,a.examine_name
        FROM report_periodic_file As a 
        WHERE a.is_delete=0 AND examine_state=1 ORDER BY a.create_time DESC;
    """
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, [], page=page, page_size=page_size)
    for row in ret['data']:
        row['pdf_file'] = STATIC_FILE_HOST + row['filepath']
    return AllResponse.operate_successfully(data=ret)


# 审核参数验证类
class ExamineItem(BaseModel):
    rid: int
    examine_state: int


# 审核定期报告文件(管理员，小组长)，此处没有对审核人员身份做验证
@period_report_api.put('/examine/')
def examine_periodic_report_file(ruizy_token: str = Header(...), reports: List[ExamineItem] = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 查询操作者信息
    with FAConnection() as cursor:
        cursor.execute('SELECT id,admin_name FROM ruizy_user WHERE id=%s LIMIT 1;', (person['uid'],))
        examiner = cursor.fetchone()
        if not examiner:
            return AllResponse.validate_error(msg='您不能进行审核操作.')
    examine_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    examine_reports = jsonable_encoder(reports)
    for r in examine_reports:
        r['examine_time'] = examine_time
        r['examine_name'] = examiner['admin_name']
    with FAConnection() as cursor:
        c = cursor.executemany('UPDATE report_periodic_file SET examine_time=%(examine_time)s,examine_name=%(examine_name)s,examine_state=%(examine_state)s WHERE id=%(rid)s;',
                               examine_reports)
    return AllResponse.operate_successfully(msg='审核成功，改变{}个数据.'.format(c))


# 导出晨会纪要PDF的类
class EarlyReferencePdfExporter(object):
    PAGE_WIDTH, PAGE_HEIGHT = A4
    LEFT_MARGIN, TOP_MARGIN, RIGHT_MARGIN, BOTTOM_MARGIN = (1.5 * cm, 3 * cm, 1.5 * cm, 2 * cm)
    TA_LEFT, TA_CENTER, TA_RIGHT, TA_JUSTIFY = (0, 1, 2, 4)

    def __init__(self, file_date: datetime.date, variety_data):
        self.file_date = file_date
        self.variety_data = variety_data
        self.story = []

    def page_decoration(self, canvas):
        # logo
        canvas.drawImage(pathlib.Path(FILE_STORAGE).joinpath('AppImages/periodReport/companyLogo.jpg'),
                         x=self.LEFT_MARGIN + 0.5 * cm, y=self.PAGE_HEIGHT - 0.9 * self.TOP_MARGIN + 12,
                         width=3.45 * cm, height=0.8 * cm)
        # 页眉
        canvas.drawImage(pathlib.Path(FILE_STORAGE).joinpath('AppImages/periodReport/pageTop.jpg'),
                         x=self.LEFT_MARGIN, y=self.PAGE_HEIGHT - 0.9 * self.TOP_MARGIN,
                         width=self.PAGE_WIDTH - 2 * self.LEFT_MARGIN, height=10)
        # 页脚
        canvas.drawImage(pathlib.Path(FILE_STORAGE).joinpath('AppImages/periodReport/pageBottom.jpg'),
                         x=self.LEFT_MARGIN, y=self.BOTTOM_MARGIN,
                         width=self.PAGE_WIDTH - 2 * self.LEFT_MARGIN, height=4)

        # 右上角名称
        # canvas.setFillColorRGB(0.6, 0.6, 0.6, 1)
        # canvas.setFont('SyH_N', 16)
        # canvas.drawString(x=self.PAGE_WIDTH - self.RIGHT_MARGIN - 120, y=self.PAGE_HEIGHT - 0.9 * self.TOP_MARGIN + 12,
        #                   text='瑞达期货研究院')

        # 2023.10.24:去掉名称改为二维码图片
        canvas.drawImage(pathlib.Path(FILE_STORAGE).joinpath('AppImages/PeriodReport/wxqrcode.jpg'),
                         x=self.PAGE_WIDTH - self.RIGHT_MARGIN - self.LEFT_MARGIN, y=self.PAGE_HEIGHT - 0.75 * self.TOP_MARGIN,
                         width=1.9 * cm, height=1.50 * cm)

        # 页面底信息
        canvas.setFont('SyH_B', 10)
        canvas.drawString(x=self.LEFT_MARGIN, y=self.BOTTOM_MARGIN - 12,
                          text='瑞达期货研究院')
        canvas.drawString(x=self.PAGE_WIDTH / 2 - 60, y=self.BOTTOM_MARGIN - 12,
                          text='服务热线：0595-86778969')
        canvas.drawString(x=self.PAGE_WIDTH - 140, y=self.BOTTOM_MARGIN - 12,
                          text='微信号：Yanjiufuwu')

        # 页面水印
        canvas.translate(0.25 * self.PAGE_HEIGHT, 0)
        canvas.rotate(32)
        # canvas.setStrokeColorRGB(1, 0, 0)  # 文字轮廓色彩(似乎不起作用)
        canvas.setFillColorRGB(0.6, 0.6, 0.6, 0.5)  # 文字填充色(默认就是黑色)
        # canvas.setFillAlpha(0.3)
        canvas.setFont('SyH_N', 40)
        canvas.drawString(self.LEFT_MARGIN * 4, (self.PAGE_HEIGHT - self.TOP_MARGIN - self.BOTTOM_MARGIN) / 2 - 20,
                          '瑞 达 期 货 研 究 院')

    def first_page_event(self, canvas, doc):
        canvas.saveState()
        self.page_decoration(canvas)
        canvas.restoreState()
        # print('第一页')  # 第一页运行一次

    def later_page_event(self, canvas, doc):
        canvas.saveState()
        self.page_decoration(canvas)
        canvas.restoreState()
        # print('每页出来', doc.page)  # 第二页开始每页运行一次

    def create_file(self):
        annex_folder = 'Annexes/PeriodicReport/Early/{}/'.format(self.file_date.year)
        title = '{}年{}月{}日晨会纪要观点'.format(self.file_date.year, self.file_date.month, self.file_date.day)
        file_folder = pathlib.Path(FILE_STORAGE).joinpath(annex_folder)
        if not file_folder.exists():
            file_folder.mkdir(parents=True)
        filepath = file_folder.joinpath(f'{title}.pdf')
        save_file = str(filepath).replace('\\', '/')
        doc = SimpleDocTemplate(save_file, leftMargin=self.LEFT_MARGIN, topMargin=self.TOP_MARGIN,
                                rightMargin=self.RIGHT_MARGIN, bottomMargin=self.BOTTOM_MARGIN)  # 默认页面距=inch
        self.story.append(Spacer(1, 0.5 * cm))
        # 标题
        self.story.append(Paragraph(title,
                                    ParagraphStyle(fontName='SyH_B', name='SyH_B', fontSize=18, alignment=self.TA_CENTER)))
        self.story.append(Spacer(1, 0.5 * cm))
        # 添加内容
        for g in self.variety_data:
            self.story.append(Spacer(1, cm))
            # 组别名称
            self.story.append(Paragraph('※ ' + g['name'], ParagraphStyle(fontName='SyH_B', name='SyH_B', fontSize=16,
                                                                         textColor='#fc5733', alignment=self.TA_CENTER,
                                                                         leading=30)))
            first_line_indent = 0 if g['key'] == 6 else 22
            is_variety_name = g['is_variety_name']  # 需要品种名的段落
            # 组内的品种数据
            for v in g['data']:
                self.story.append(Spacer(1, cm))
                if is_variety_name:
                    self.story.append(Paragraph(v['variety_name'], ParagraphStyle(fontName='SyH_B', name='SyH_B', fontSize=12, alignment=self.TA_LEFT, leading=18)))
                for text in v['reference'].split('\n'):  # 分段
                    self.story.append(Paragraph(text, ParagraphStyle(fontName='SyH_N', name='SyH_N', fontSize=11, alignment=self.TA_LEFT, leading=17,
                                                                     firstLineIndent=first_line_indent, wordWrap='CJK')))
        self.story.append(Spacer(1, 1 * cm))
        # 研究员信息
        research_data = [
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '研究员:', '蔡跃辉', '期货从业资格号F0251444', '期货投资咨询从业证书号Z0013101']],
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '助理研究员:', '李秋荣', '期货从业资格号F03101823', '']]
        ]
        research_data[0][1] = Paragraph('研究员:', ParagraphStyle(fontName='SyH_B', name='Song', fontSize=9, alignment=0))
        research_data[1][1] = Paragraph('助理研究员:', ParagraphStyle(fontName='SyH_B', name='Song', fontSize=9, alignment=0))
        research_style = [
            # ('GRID', (1, 0), (-1, -1), 1, '#000000'),
            ('ALIGN', (1, 0), (1, -1), 'RIGHT'),
        ]
        # print(self.PAGE_WIDTH - self.LEFT_MARGIN - self.RIGHT_MARGIN)
        self.story.append(Table(research_data, style=TableStyle(research_style), colWidths=(135, 60, 40, 120, 155)))
        self.story.append(Spacer(1, 2 * cm))
        # 推广图片
        qr_code_data = [[Image(pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/linkqrcode.png'),
                               width=460, height=130, useDPI=True)]]
        self.story.append(Table(qr_code_data, style=TableStyle([('ALIGN', (0, 0), (-1, -1), 'CENTER')]), colWidths=[568]))
        self.story.append(Spacer(1, 1.5 * cm))
        self.story.append(Paragraph('免责声明', ParagraphStyle(fontName='SyH_B', name='SyH_B', fontSize=14)))
        text = "本报告中的信息均来源于公开可获得资料，瑞达期货股份有限公司力求准确可靠，但对这些信息的准确性及完整性不做任何保证，据此投资，责任自负。本报告不构成个人投资建议，客户应考虑本报告中的任何意见或建议是否符合其特定状况。本报告版权仅为我公司所有，未经书面许可，任何机构和个人不得以任何形式翻版、复制和发布。如引用、刊发，需注明出处为瑞达期货股份有限公司研究院，且不得对本报告进行有悖原意的引用、删节和修改。"
        self.story.append(Paragraph(text, ParagraphStyle(fontName='SyH_N', name='SyH_N', fontSize=10, leading=16,
                                                         spaceBefore=14, firstLineIndent=22)))
        doc.build(self.story, onFirstPage=self.first_page_event, onLaterPages=self.later_page_event)
        return str(pathlib.Path(annex_folder).joinpath(f'{title}.pdf')).replace('\\', '/')


# 导出晨会纪要word的类
class EarlyReferenceWordExporter(object):

    def __init__(self, file_date: datetime.date, variety_data):
        self.file_date = file_date
        self.variety_data = variety_data
        # 使用模板文件
        self.document = Document(str(pathlib.Path(FILE_STORAGE).joinpath('AppFiles/templates/EarlyTemplate.docx')))

    def create_file(self):
        annex_folder = 'Annexes/PeriodicReport/Early/{}/'.format(self.file_date.year)
        title = '{}年{}月{}日晨会纪要观点'.format(self.file_date.year, self.file_date.month, self.file_date.day)
        file_folder = pathlib.Path(FILE_STORAGE).joinpath(annex_folder)
        if not file_folder.exists():
            file_folder.mkdir(parents=True)
        filepath = file_folder.joinpath(f'{title}.docx')
        save_file = str(filepath).replace('\\', '/')
        self.add_head(head_text=title)
        # 添加内容
        for g in self.variety_data:
            # 组别名称
            self.add_paragraph_text('\n', first_line_indent=0, blank_line=False)
            self.add_group(group_name='※ ' + g['name'])
            first_line_indent = 0 if g['key'] == 6 else 24
            is_variety_name = g['is_variety_name']  # 需要品种名的段落
            # 组内的品种数据
            for v in g['data']:
                if is_variety_name:
                    self.add_paragraph_text('', first_line_indent=0, blank_line=False)
                    self.add_paragraph_name(paragraph_name=v['variety_name'])
                for text in v['reference'].strip().split('\n'):  # 分段
                    text = text.strip()
                    self.add_paragraph_text(text=text.strip(), first_line_indent=first_line_indent, blank_line=False)
        # 添加图片和免责声明
        self.add_picture()
        self.add_explain()
        self.document.save(save_file)
        return str(pathlib.Path(annex_folder).joinpath(f'{title}.docx')).replace('\\', '/')

    def add_head(self, head_text):
        paragraph = self.document.add_paragraph()
        paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        paragraph_run = paragraph.add_run(head_text)
        paragraph_run.font.name = '宋体'
        paragraph_run.font.color.rgb = RGBColor(0, 0, 0)
        paragraph_run.font.size = Pt(18)
        paragraph_run.bold = True
        paragraph_run.font.element.rPr.rFonts.set(qn('w:eastAsia'), '宋体')

    def add_group(self, group_name):
        paragraph = self.document.add_paragraph()
        paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        paragraph_run = paragraph.add_run(group_name)
        paragraph_run.font.name = '宋体'
        paragraph_run.font.color.rgb = RGBColor(252, 87, 51)
        paragraph_run.font.size = Pt(16)
        paragraph_run.bold = True
        paragraph_run.font.element.rPr.rFonts.set(qn('w:eastAsia'), '宋体')

    def add_paragraph_name(self, paragraph_name):
        paragraph = self.document.add_paragraph()
        paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.LEFT
        paragraph_run = paragraph.add_run(paragraph_name)
        paragraph_run.font.name = '宋体'
        paragraph_run.font.color.rgb = RGBColor(0, 0, 0)
        paragraph_run.font.size = Pt(12)
        paragraph_run.bold = True
        paragraph_run.font.element.rPr.rFonts.set(qn('w:eastAsia'), '宋体')

    def add_paragraph_text(self, text, first_line_indent=24, blank_line=True):
        paragraph = self.document.add_paragraph()
        paragraph.paragraph_format.first_line_indent = Pt(first_line_indent)
        paragraph.paragraph_format.line_spacing = Pt(24)  # 行高

        paragraph_run = paragraph.add_run(text)
        paragraph_run.font.name = '宋体'
        paragraph_run.font.color.rgb = RGBColor(0, 0, 0)
        paragraph_run.font.size = Pt(12)
        paragraph_run.font.element.rPr.rFonts.set(qn('w:eastAsia'), '宋体')
        if blank_line:
            self.document.add_paragraph("")

    def add_picture(self):
        self.document.add_paragraph("")
        image_url = pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/linkqrcode.png')
        self.document.add_picture(str(image_url), width=Cm(15), height=Cm(4))  # 此处返回的对象设置居中无法生效
        self.document.paragraphs[-1].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER  # 此时它位于最后一个段落，设置居中格式

    def add_explain(self):
        self.add_paragraph_name(paragraph_name='免责声明')
        t = """本报告中的信息均来源于公开可获得资料，瑞达期货股份有限公司力求准确可靠，但对这些信息的准确性及完整性不做任何保证，据此投资，责任自负。本报告不构成个人投资建议，客户应考虑本报告中的任何意见或建议是否符合其特定状况。本报告版权仅为我公司所有，未经书面许可，任何机构和个人不得以任何形式翻版、复制和发布。如引用、刊发，需注明出处为瑞达期货股份有限公司研究院，且不得对本报告进行有悖原意的引用、删节和修改。"""
        paragraph = self.document.add_paragraph()
        paragraph.paragraph_format.first_line_indent = Pt(20)
        paragraph_run = paragraph.add_run(t)
        paragraph_run.font.name = '宋体'
        paragraph_run.font.color.rgb = RGBColor(0, 0, 0)
        paragraph_run.font.size = Pt(12)
        paragraph_run.font.element.rPr.rFonts.set(qn('w:eastAsia'), '宋体')


class EarlyReferenceItem(BaseModel):
    variety_en: str
    create_date: str
    reference: constr(min_length=1)

    @validator('create_date')
    def validate_create_date(cls, value):
        if not datetime_utils.auth_datetime_string(value, f='%Y%m%d'):
            raise ValueError('不合法的create_date类型，接受YYYYMMDD形式')
        return value

    @validator('variety_en')
    def validate_variety_en(cls, value):
        if value not in VARIETY.VARIETY_NAMES.keys():
            raise ValueError('不合法的品种名称')
        return value


class AdminCreateEarlyReferenceItem(EarlyReferenceItem):
    user_id: int


# 创建晨会纪要
@period_report_api.post('/early/')
def create_early_reference(ruizy_token: str = Header(...), references: List[EarlyReferenceItem] = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    first_obj = references[0]
    if first_obj.create_date < datetime_utils.today():
        return AllResponse.validate_error(msg='历史的晨会纪要不能操作了.')
    # 查询存在与否
    data_list = jsonable_encoder(references)
    for item in data_list:
        item['user_id'] = person['uid']
    up_sql = """
        UPDATE work_early_reference SET reference=%(reference)s,user_id=%(user_id)s
        WHERE create_date=%(create_date)s AND variety_en=%(variety_en)s;
    """
    if security.operator_is_super_admin(person):  # 超级管理员更新时。不改变数据条目的作者
        up_sql = """
            UPDATE work_early_reference SET reference=%(reference)s
            WHERE create_date=%(create_date)s AND variety_en=%(variety_en)s;
        """
    insert_sql = """
        INSERT IGNORE INTO work_early_reference (variety_en,create_date,reference,user_id) 
        VALUES (%(variety_en)s,%(create_date)s,%(reference)s,%(user_id)s);
    """
    with FAConnection() as cursor:
        up_count = cursor.executemany(up_sql, data_list)
        insert_count = cursor.executemany(insert_sql, data_list)
    message = '操作成功，更新{}个数据，新增{}个数据.'.format(up_count, insert_count)
    return AllResponse.operate_successfully(msg=message)


# 管理员创建一个晨会纪要
@period_report_api.post('/earlyAdminCreate/')
def admin_create_early_reference(ruizy_token: str = Header(...), reference: AdminCreateEarlyReferenceItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if reference.create_date < datetime_utils.today():
        return AllResponse.validate_error(msg='历史的晨会纪要不能操作了.')
    if not security.operator_is_super_admin(person):
        return AllResponse.validate_error(msg='您不能进行这个操作.')
    up_sql = """
        UPDATE work_early_reference SET reference=%(reference)s,user_id=%(user_id)s
        WHERE create_date=%(create_date)s AND variety_en=%(variety_en)s;
    """
    insert_sql = """
        INSERT IGNORE INTO work_early_reference (variety_en,create_date,reference,user_id) 
        VALUES (%(variety_en)s,%(create_date)s,%(reference)s,%(user_id)s);
    """
    with FAConnection() as cursor:
        up_count = cursor.execute(up_sql, jsonable_encoder(reference))
        insert_count = cursor.execute(insert_sql, jsonable_encoder(reference))
    message = '单个操作成功，更新{}个数据，新增{}个数据.'.format(up_count, insert_count)
    return AllResponse.operate_successfully(msg=message)


# 查询指定日期的晨会纪要
@period_report_api.get('/early/')
def query_early_reference(ruizy_token: str = Header(...), date: str = Query(...), uid: int = Query(None),
                          exporter: str = Query(None), include_all: int = Query(1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not datetime_utils.auth_datetime_string(date, f='%Y%m%d'):
        return AllResponse.validate_error(msg='日期格式错误，接受YYYYMMDD类型')
    if uid is not None:  # 管理员查询其他用户的
        if security.operator_is_manager(person):
            person['uid'] = uid
    else:
        if security.operator_is_super_admin(person) or person['uid'] in [21]:
            person['uid'] = 0  # 不传uid，且查询者是超级管理员，查询所有
    # 查询
    if person['uid'] == 0:  # 查询今日所有
        sql = 'SELECT id,variety_en,create_date,reference,mark_state,mark_text,user_id FROM work_early_reference WHERE create_date=%s;'
        prm = [date]
    else:
        # 查询指定uid的今日数据
        sql = 'SELECT id,variety_en,create_date,reference,user_id,mark_state,mark_text FROM work_early_reference WHERE user_id=%s AND create_date=%s;'
        prm = [person['uid'], date]
    with FAConnection() as cursor:
        cursor.execute(sql, prm)
        records = cursor.fetchall()
    for row in records:
        row['variety_name'] = VARIETY.VARIETY_NAMES.get(row['variety_en'], row['variety_en'])

    group1 = ['RFIEC']  # 财经概述
    group2 = ['RIFHC', 'RZTGZ', 'RUSCN', 'RUSDX', 'RZZSP', 'EC']  # 股指国债
    group3 = ['A', 'B', 'AP', 'C', 'CF', 'CJ', 'CS', 'JD', 'LH', 'M', 'OI', 'P', 'PK', 'RM', 'SR', 'Y']  # 农产品
    group4 = ['BU', 'EB', 'EG', 'FG', 'FU', 'L', 'MA', 'PF', 'PG', 'PP', 'BR', 'RU', 'SA', 'SH', 'SC', 'SP', 'PX', 'TA', 'UR', 'V']  # 化工品
    group5 = ['AL', 'AO', 'CU', 'HC', 'I', 'J', 'JM', 'LC', 'NI', 'PB', 'RAUAG', 'RB', 'SF', 'SI', 'SM', 'SN', 'SS', 'ZC', 'ZN']  # 有色黑色
    group6 = ['RFOCU']  # 每日关注

    groups = [
        {'name': '财经概述', 'key': 1, 'varieties': group1, 'is_variety_name': True},
        {'name': '股指国债', 'key': 2, 'varieties': group2, 'is_variety_name': True},
        {'name': '农产品', 'key': 3, 'varieties': group3, 'is_variety_name': True},
        {'name': '化工品', 'key': 4, 'varieties': group4, 'is_variety_name': True},
        {'name': '有色黑色', 'key': 5, 'varieties': group5, 'is_variety_name': True},
        {'name': '每日关注', 'key': 6, 'varieties': group6, 'is_variety_name': False}
    ]
    # 组织数据
    data_dict = {v['variety_en']: v for v in records}
    data_list = []
    for group in groups:
        g_data = {
            'name': group['name'],
            'key': group['key'],
            'is_variety_name': group['is_variety_name'],
            'data': []
        }
        for v in group['varieties']:
            if data_dict.get(v):
                g_data['data'].append(data_dict.get(v))
            else:
                if include_all:
                    g_data['data'].append({'id': -1, 'variety_en': v, 'variety_name': VARIETY.VARIETY_NAMES.get(v, v),
                                           'reference': '', 'user_id': -1})  # 主要是为了显示没填写的品种
        if len(g_data['data']) > 0:
            data_list.append(g_data)
    file_date = datetime_utils.format_integer_date(date, obj=True)
    # 生成pdf
    if exporter == 'pdf':
        handler = EarlyReferencePdfExporter(file_date=file_date, variety_data=data_list)
        filepath = handler.create_file()
        # 写入定期报告数据库文件记录
        # 查询是否存在
        title = '{}年{}月{}日晨会纪要观点'.format(file_date.year, file_date.month, file_date.day)
        author_id = 93 if person['uid'] == 0 else person['uid']  # 如果是0，则表示超级管理导出，此时赋值给研究院的号id=93
        new_sql = """
            INSERT INTO report_periodic_file (file_date,creator_id,variety_en,title,file_type,filepath,examine_state)
            VALUES (%s,%s,%s,%s,%s,%s,%s);
        """
        new_prm = [date, author_id, constant.SPACE_VARIETY_ALL, title, 'EARLY', filepath, 1]
        with FAConnection() as cursor:
            cursor.execute('SELECT id FROM report_periodic_file WHERE file_date=%s AND file_type="EARLY" LIMIT 1;', [date])
            obj = cursor.fetchone()
            if obj:
                # 数据库更新。
                cursor.execute('UPDATE report_periodic_file SET creator_id=%s,filepath=%s WHERE id=%s LIMIT 1;',
                               [author_id, filepath, obj['id']])
            else:
                # 插入数据
                cursor.execute(new_sql, new_prm)
        return AllResponse.operate_successfully(data={'file_url': STATIC_FILE_HOST + filepath})
    elif exporter == 'word':
        handler = EarlyReferenceWordExporter(file_date=file_date, variety_data=data_list)
        filepath = handler.create_file()
        # word文档不做数据库保存
        return AllResponse.operate_successfully(data={'file_url': STATIC_FILE_HOST + filepath})
    else:
        return AllResponse.operate_successfully(data=data_list)


# 删除品种晨会纪要条目
@period_report_api.delete('/early/')
def delete_early_reference(ruizy_token: str = Header(...), rid: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = "DELETE FROM work_early_reference WHERE id=%s "
    prm = [rid]
    if security.operator_is_manager(person):
        sql += ';'
    else:
        sql += 'AND user_id=%s;'
        prm.append(person['uid'])
    with FAConnection() as cursor:
        # 查询一下是否是今日的
        cursor.execute('SELECT id,create_date FROM work_early_reference WHERE id=%s LIMIT 1;', [rid])
        obj = cursor.fetchone()
        if str(obj['create_date']) != str(datetime_utils.today()):
            return AllResponse.validate_error(msg='只能删除当日的晨会纪要条目.')
        cursor.execute(sql, prm)
    return AllResponse.operate_successfully(msg='删除成功!')


class MarkEarlyReference(BaseModel):
    record_id: int
    mark_text: str


@period_report_api.post('/early/mark/')  # 标记晨会纪要
def mark_early_reference(ruizy_token: str = Header(...), mark: MarkEarlyReference = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = "UPDATE work_early_reference SET mark_state=%s,mark_text=%s WHERE id=%s LIMIT 1;"
    state = 1 if mark.mark_text.strip() else 0
    with FAConnection() as cursor:
        cursor.execute(sql, [state, mark.mark_text.strip(), mark.record_id])
    return AllResponse.operate_successfully(msg='标记晨会纪要成功!')


@period_report_api.get('/early/mark/')  # 查询标记的晨会纪要
def mark_early_reference_get(start: str = Query(...), end: str = Query(...), state: int = Query(1)):
    start_date = datetime_utils.auth_datetime_string(start, '%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(end, '%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='请求参数错误，请设置合适的日期格式')
    sql = """
        SELECT a.create_date,a.variety_en,a.mark_text,a.mark_state,a.reference,a.user_id,b.admin_name 
        FROM work_early_reference As a INNER JOIN ruizy_user As b ON a.user_id=b.id
        WHERE a.create_date>=%s AND a.create_date<=%s AND mark_state=%s
        ORDER BY a.create_date DESC;
    """
    with FAConnection() as cursor:
        cursor.execute(sql,  [start_date, end_date, state])
        rows = list(cursor.fetchall())
    # 按人员统计
    user_dict = {}
    for item in rows:
        item['variety_name'] = VARIETY.VARIETY_NAMES.get(item['variety_en'])
        if item['user_id'] in user_dict.keys():
            _obj = user_dict[item['user_id']]
            _obj['total_count'] += 1
            _obj['rows'].append(item)
        else:
            user_dict[item['user_id']] = {
                'user_id': item['user_id'],
                'admin_name': item['admin_name'],
                'total_count': 1,
                'rows': [item]
            }
    data_list = list(user_dict.values())
    data_list.sort(key=lambda x: x['total_count'], reverse=True)
    return AllResponse.operate_successfully(data=data_list)


# 上传日报文件
@period_report_api.post('/daily/upload/')
async def upload_daily_file(author_id: int = Form(...), variety_en: str = Form(...), file_date: str = Form(...),
                            file: UploadFile = Form(...)):
    file_date = datetime_utils.auth_datetime_string(file_date, f='%Y%m%d')
    if not file_date:
        return AllResponse.validate_error(msg='日期格式有误，接受YYYYMMDD类型.')
    if variety_en not in VARIETY.VARIETY_NAMES.keys():
        return AllResponse.validate_error(msg='不合法的品种代码')
    # 查询当天的日报数据是否已经存在
    with FAConnection() as cursor:
        cursor.execute("SELECT id FROM report_periodic_file WHERE file_type='DAILY' AND file_date=%s AND variety_en=%s LIMIT 1;",
                       (file_date, variety_en))
        is_exist = cursor.fetchone()
    if is_exist:
        return AllResponse.no_do_anything(msg='{}的{}日报已经存在，无需重复上传!'.format(file_date, VARIETY.VARIETY_NAMES.get(variety_en, variety_en)))
    # 保存文件
    save_path, sql_path = file_utils.get_file_paths(module_name='PeriodicReport/Daily', filename=file.filename, date_folder=file_date, hashed=True)
    title = char_utils.strQ2B(file.filename)  # 转为半角
    title = file_utils.remove_filename_number_suffix(title, number_max=10)  # 去除数字编码和后缀
    content = await file.read()  # 将文件保存到目标位置
    with open(save_path, "wb") as fp:
        fp.write(content)
    # 添加数据库记录
    insert_sql = """
        INSERT INTO report_periodic_file (file_date,creator_id,variety_en,title,file_type,filepath,examine_name,examine_state)
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s);
    """
    insert_prm = (file_date, author_id, variety_en, title, 'DAILY', sql_path, '-', 1)
    with FAConnection() as cursor:
        c = cursor.execute(insert_sql, insert_prm)
    if c < 1:
        # 移除文件
        tmp_file = pathlib.Path(save_path)
        if tmp_file.exists() and tmp_file.is_file():
            tmp_file.unlink()
    msg = '上传日报文件: {}成功!'.format(file.filename) if c else '上传日报完成，但没有添加文件.'
    return AllResponse.operate_successfully(msg=msg)


# 上传周报文件
@period_report_api.post('/weekly/upload/')
async def upload_weekly_file(author_id: int = Form(...), variety_en: str = Form(...), file_date: str = Form(...),
                             file: UploadFile = Form(...)):
    file_date = datetime_utils.auth_datetime_string(file_date, f='%Y%m%d')
    if not file_date:
        return AllResponse.validate_error(msg='日期格式有误，接受YYYYMMDD类型.')
    if variety_en not in VARIETY.VARIETY_NAMES.keys():
        return AllResponse.validate_error(msg='{}是不合法的品种代码'.format(variety_en))
    # 查询当天的周报数据是否已经存在
    with FAConnection() as cursor:
        cursor.execute("SELECT id FROM report_periodic_file WHERE file_type='WEEKLY' AND file_date=%s AND variety_en=%s LIMIT 1;",
                       (file_date, variety_en))
        is_exist = cursor.fetchone()
    if is_exist:
        return AllResponse.no_do_anything(msg='{}的{}周报已经存在，无需重复上传!'.format(file_date, VARIETY.VARIETY_NAMES.get(variety_en, variety_en)))
    # 保存文件
    week_folder = datetime.date.today().strftime('%Y%W')
    save_path, sql_path = file_utils.get_file_paths(module_name='PeriodicReport/Weekly', filename=file.filename, date_folder=week_folder, hashed=True)
    # 转为半角
    title = char_utils.strQ2B(file.filename)
    title = file_utils.remove_filename_number_suffix(title, number_max=10)
    content = await file.read()  # 将文件保存到目标位置
    with open(save_path, "wb") as fp:
        fp.write(content)
    # 添加数据库记录
    insert_sql = """
        INSERT INTO report_periodic_file (file_date,creator_id,variety_en,title,file_type,filepath,examine_name,examine_state)
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s);
    """
    insert_prm = (file_date, author_id, variety_en, title, 'WEEKLY', sql_path, '-', 1)
    with FAConnection() as cursor:
        c = cursor.execute(insert_sql, insert_prm)
    if c < 1:
        # 移除文件
        tmp_file = pathlib.Path(save_path)
        if tmp_file.exists() and tmp_file.is_file():
            tmp_file.unlink()
    msg = '上传周报文件: {}成功!'.format(file.filename) if c else '上传周报完成，但没有添加文件.'
    return AllResponse.operate_successfully(msg=msg)


# 上传月报文件
@period_report_api.post('/monthly/upload/')
async def upload_weekly_file(ruizy_token: str = Header(...),
                             author_id: int = Form(0), variety_en: str = Form(...), file_date: str = Form(...),
                             file: UploadFile = Form(...)):
    import time
    time.sleep(2)
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if author_id == 0:
        author_id = person['uid']
    file_date = datetime_utils.auth_datetime_string(file_date, f='%Y%m%d')
    if not file_date:
        return AllResponse.validate_error(msg='日期格式有误，接受YYYYMMDD类型.')
    if variety_en not in VARIETY.VARIETY_NAMES.keys():
        return AllResponse.validate_error(msg='{}是不合法的品种代码'.format(variety_en))
    # 查询当天的月报数据是否已经存在
    with FAConnection() as cursor:
        cursor.execute("SELECT id FROM report_periodic_file WHERE file_type='MONTHLY' AND file_date=%s AND variety_en=%s LIMIT 1;",
                       (file_date, variety_en))
        is_exist = cursor.fetchone()
    if is_exist:
        return AllResponse.no_do_anything(msg='{}的{}月报已经存在，无需重复上传!'.format(file_date, VARIETY.VARIETY_NAMES.get(variety_en, variety_en)))
    # 保存文件
    save_path, sql_path = file_utils.get_file_paths(module_name='PeriodicReport/Monthly', filename=file.filename, hashed=True)
    # 名称转为半角
    title = char_utils.strQ2B(file.filename)
    title = file_utils.remove_filename_number_suffix(title, number_max=10)  # 去除数据编号和后缀
    content = await file.read()  # 将文件保存到目标位置
    with open(save_path, "wb") as fp:
        fp.write(content)
    # 添加数据库记录
    insert_sql = """
        INSERT INTO report_periodic_file (file_date,creator_id,variety_en,title,file_type,filepath,examine_name,examine_state)
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s);
    """
    insert_prm = (file_date, author_id, variety_en, title, 'MONTHLY', sql_path, '-', 1)
    with FAConnection() as cursor:
        c = cursor.execute(insert_sql, insert_prm)
    if c < 1:
        # 移除文件
        tmp_file = pathlib.Path(save_path)
        if tmp_file.exists() and tmp_file.is_file():
            tmp_file.unlink()
    msg = '上传月报文件: {}成功!'.format(file.filename) if c else '上传月报完成，但没有添加文件.'

    return AllResponse.operate_successfully(msg=msg)


@period_report_api.post('/monthly/sliceUpload/')  # 分片上传月报文件
async def slice_upload_monthly(
        ruizy_token: str = Header(...),
        chunk_index: int = Form(...),
        chunk_count: int = Form(...),
        chunk_data: UploadFile = Form(...),
        file_date: str = Form(...),
        file_vcode: str = Form(...),
        file_name: str = Form(...)
):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 使用user_id,file_date,vcode,filename哈希出缓存文件夹
    cache_folder = char_utils.hash_string(src_string=file_name,
                                          salt={'uid': str(person['uid']), 'vcode': file_vcode, 'date': file_date, 'file_type': 'MONTHLY'})
    abs_cache_folder = pathlib.Path(f'{FILE_STORAGE}').joinpath('Temp').joinpath(cache_folder)
    if not abs_cache_folder.exists():
        abs_cache_folder.mkdir(parents=True)

    cache_file = f'{chunk_index}_{chunk_count}_{file_date.replace("-", "")}_{file_vcode}_MONTHLY.tmp'
    with open(abs_cache_folder.joinpath(cache_file), 'wb') as f:
        f.write(await chunk_data.read())

    return AllResponse.operate_successfully()


class MonthlyFileMerge(BaseModel):
    file_date: str
    file_vcode: str
    file_name: str


@period_report_api.post('/monthly/mergeUpload/')  # 请求文件合并写入数据库
def merge_upload_monthly(ruizy_token: str = Header(...), merge: MonthlyFileMerge = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    cache_folder = char_utils.hash_string(src_string=merge.file_name,
                                          salt={'uid': str(person['uid']), 'vcode': merge.file_vcode, 'date': merge.file_date, 'file_type': 'MONTHLY'})
    abs_cache_folder = pathlib.Path(f'{FILE_STORAGE}').joinpath('Temp').joinpath(cache_folder)
    if not abs_cache_folder.exists():
        return AllResponse.no_do_anything(msg='合并失败，文件不存在')

    # 保存文件
    save_path, sql_path = file_utils.get_file_paths(module_name='PeriodicReport/Monthly', filename=merge.file_name, hashed=True)
    with open(save_path, 'ab+') as fp:
        for slice_file in sorted(abs_cache_folder.glob('*.tmp')):
            with open(slice_file, 'rb') as fs:
                fp.write(fs.read())

    # 写入SQL
    # 名称转为半角
    title = char_utils.strQ2B(merge.file_name)
    title = file_utils.remove_filename_number_suffix(title, number_max=10)  # 去除数据编号和后缀
    # 添加数据库记录
    insert_sql = """
        INSERT INTO report_periodic_file (file_date,creator_id,variety_en,title,file_type,filepath)
        VALUES (%s,%s,%s,%s,%s,%s);
    """
    insert_prm = (merge.file_date.replace('-', ''), person['uid'], merge.file_vcode, title, 'MONTHLY', sql_path)
    with FAConnection() as cursor:
        c = cursor.execute(insert_sql, insert_prm)
    msg = '合并月报文件: {}成功!'.format(merge.file_name)
    res = True
    if c < 1:
        # 移除文件
        tmp_file = pathlib.Path(save_path)
        if tmp_file.exists() and tmp_file.is_file():
            tmp_file.unlink()
        msg = '合并月报完成，但没有添加文件.'
        res = False
    return AllResponse.operate_successfully(data={'merge': res, 'msg': msg})


@period_report_api.get('/monthly/belong/')  # 查询用户上传的报告
def belong_monthly_report(ruizy_token: str = Header(...), page: int = Query(1), page_size: int = Query(50), isd: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        SELECT id,create_time,file_date,creator_id,variety_en,title,file_type,filepath,examine_time,examine_name,examine_state,examine_note,
            dept_examine_name,dept_examine_state,dept_examine_note,is_delete
        FROM report_periodic_file WHERE creator_id=%s AND file_type='MONTHLY' AND is_delete=%s ORDER BY create_time DESC,file_date DESC;
    """
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, param=[person['uid'], isd], page=page, page_size=page_size)
    for row in ret['data']:
        row['create_time'] = str(row['create_time'])
        row['file_date'] = datetime_utils.format_integer_date(row['file_date'], rf='%Y-%m-%d', obj=False)
        row['variety_name'] = VARIETY.VARIETY_NAMES.get(row['variety_en'])
        row['examine_text'] = constant.EXAMINE_STATES.get(row['examine_state'])
        row['dept_examine_text'] = constant.EXAMINE_STATES.get(row['dept_examine_state'])
        row['file_url'] = STATIC_FILE_HOST + row['filepath']
    return AllResponse.operate_successfully(data=ret)


# 上传年报文件
@period_report_api.post('/annual/upload/')
async def upload_weekly_file(author_id: int = Form(...), variety_en: str = Form(...), file_date: str = Form(...),
                             file: UploadFile = Form(...)):
    file_date = datetime_utils.auth_datetime_string(file_date, f='%Y%m%d')
    if not file_date:
        return AllResponse.validate_error(msg='日期格式有误，接受YYYYMMDD类型.')
    if variety_en not in VARIETY.VARIETY_NAMES.keys():
        return AllResponse.validate_error(msg='{}是不合法的品种代码'.format(variety_en))
    # 查询当天的年报数据是否已经存在
    with FAConnection() as cursor:
        cursor.execute("SELECT id FROM report_periodic_file WHERE file_type='ANNUAL' AND file_date=%s AND variety_en=%s LIMIT 1;",
                       (file_date, variety_en))
        is_exist = cursor.fetchone()
    if is_exist:
        return AllResponse.no_do_anything(msg='{}的{}年报已经存在，无需重复上传!'.format(file_date, VARIETY.VARIETY_NAMES.get(variety_en, variety_en)))
    # 保存文件
    save_path, sql_path = file_utils.get_file_paths(module_name='PeriodicReport/Annual', filename=file.filename, hashed=True)
    # 名称转为半角
    title = char_utils.strQ2B(file.filename)
    title = file_utils.remove_filename_number_suffix(title, number_max=10)  # 去除数据编号和后缀
    content = await file.read()  # 将文件保存到目标位置
    with open(save_path, "wb") as fp:
        fp.write(content)
    # 添加数据库记录
    insert_sql = """
        INSERT INTO report_periodic_file (file_date,creator_id,variety_en,title,file_type,filepath,examine_name,examine_state)
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s);
    """
    insert_prm = (file_date, author_id, variety_en, title, 'ANNUAL', sql_path, '-', 1)
    with FAConnection() as cursor:
        c = cursor.execute(insert_sql, insert_prm)
    if c < 1:
        # 移除文件
        tmp_file = pathlib.Path(save_path)
        if tmp_file.exists() and tmp_file.is_file():
            tmp_file.unlink()
    msg = '上传年报文件: {}成功!'.format(file.filename) if c else '上传年报完成，但没有添加文件.'
    return AllResponse.operate_successfully(msg=msg)
